-- liquibase formatted sql

-- changeset torkov:create_schema_05

CREATE TABLE IF NOT EXISTS public.tpp_ref_account_type
(
    internal_id serial PRIMARY KEY,
    value       VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS public.tpp_ref_product_class
(
    internal_id      serial PRIMARY KEY,
    value            VARCHAR(100) UNIQUE NOT NULL,
    gbi_code         VARCHAR(50),
    gbi_name         VARCHAR(100),
    product_row_code VARCHAR(50),
    product_row_name VARCHAR(100),
    subclass_code    VARCHAR(50),
    subclass_name    VARCHAR(100)
);


CREATE TABLE IF NOT EXISTS public.tpp_ref_product_register_type
(
    internal_id              serial PRIMARY KEY,
    value                    VARCHAR(100) UNIQUE NOT NULL,
    register_type_name       VARCHAR(100)        NOT NULL,
    product_class_code       VARCHAR(50)         NOT NULL,
    register_type_start_date TIMESTAMP,
    register_type_end_date   TIMESTAMP,
    account_type             VARCHAR(50)
);
ALTER TABLE public.tpp_ref_product_register_type
    ADD FOREIGN KEY (product_class_code) REFERENCES public.tpp_ref_product_class (value);

ALTER TABLE public.tpp_ref_product_register_type
    ADD FOREIGN KEY (account_type) REFERENCES public.tpp_ref_account_type (value);

CREATE TABLE IF NOT EXISTS public.tpp_product_register
(
    id             serial PRIMARY KEY,
    product_id     BIGINT,
    type           VARCHAR(50) NOT NULL,
    account        BIGINT,
    currency_code  VARCHAR(30),
    state          VARCHAR(50),
    account_number VARCHAR(25)
);

ALTER TABLE public.tpp_product_register
    ADD FOREIGN KEY (type) REFERENCES public.tpp_ref_product_register_type (value);

CREATE TABLE IF NOT EXISTS public.account_pool
(
    id                 serial PRIMARY KEY,
    branch_code        VARCHAR(50),
    currency_code      VARCHAR(30),
    mdm_code           VARCHAR(50),
    priority_code      VARCHAR(30),
    registry_type_code VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS public.account
(
    id              serial PRIMARY KEY,
    account_pool_id BIGINT,
    account_number  VARCHAR(25),
    bussy           BOOLEAN
);

ALTER TABLE public.account
    ADD FOREIGN KEY (account_pool_id) REFERENCES public.account_pool (id);
--ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS public.tpp_template_register_balance
(
    id               serial PRIMARY KEY,
    register_id      BIGINT,
    amount           DECIMAL,
    "order"          VARCHAR(50),
    last_modify_date TIMESTAMP
);

CREATE TABLE IF NOT EXISTS public.tpp_product
(
    id                 serial PRIMARY KEY,
--	agreement_id BIGINT,
    product_code_id    BIGINT,
    client_id          BIGINT,
    type               VARCHAR(50),
    number             VARCHAR(50),
    priority           BIGINT,
    date_of_conclusion TIMESTAMP,
    start_date_time    TIMESTAMP,
    end_date_time      TIMESTAMP,
    days               BIGINT,
    penalty_rate       DECIMAL,
    nso                DECIMAL,
    threshold_amount   DECIMAL,
    requisite_type     VARCHAR(50),
    interest_rate_type VARCHAR(50),
    tax_rate           DECIMAL,
    reasone_close      VARCHAR(100),
    state              VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS public.agreement
(
    id                                       serial PRIMARY KEY,
    product_id                               BIGINT,
    general_agreement_id                     VARCHAR(50),
    supplementary_agreement_id               VARCHAR(50),
    arrangement_type                         VARCHAR(50),
    sheduler_job_id                          BIGINT,
    number                                   VARCHAR(50),
    opening_date                             TIMESTAMP,
    closing_date                             TIMESTAMP,
    cancel_date                              TIMESTAMP,
    validity_duration                        BIGINT,
    cancellation_reason                      VARCHAR(100),
    status                                   VARCHAR(50),
    interest_calculation_date                TIMESTAMP,
    interest_rate                            DECIMAL,
    coefficient                              DECIMAL,
    coefficient_action                       VARCHAR(50),
    minimum_interest_rate                    DECIMAL,
    minimum_interest_rate_coefficient        DECIMAL,
    minimum_interest_rate_coefficient_action VARCHAR(50),
    maximal_interest_rate                    DECIMAL,
    maximal_interest_rate_coefficient        DECIMAL,
    maximal_interest_rate_coefficient_action VARCHAR(50)
);

ALTER TABLE public.agreement
    ADD FOREIGN KEY (product_id) REFERENCES public.tpp_product (id);

CREATE SEQUENCE IF NOT EXISTS public.table_id_seq;

-- rollback drop table public.agreement;
-- rollback drop table public.tpp_product;
-- rollback drop table public.tpp_template_register_balance;
-- rollback drop table public.account;
-- rollback drop table public.account_pool;
-- rollback drop table public.tpp_product_register;
-- rollback drop table public.tpp_ref_product_register_type;
-- rollback drop table public.tpp_ref_product_class;
-- rollback drop table public.tpp_ref_account_type;
-- rollback drop sequence public.table_id_seq;